package com.che.software.testato.domain.dao.jdbc.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.che.software.testato.domain.dao.IHierarchyDAO;
import com.che.software.testato.domain.dao.jdbc.adao.AbstractDAO;
import com.che.software.testato.domain.dao.jdbc.exception.HierarchyCreationDAOException;
import com.che.software.testato.domain.dao.jdbc.exception.HierarchySearchDAOException;
import com.che.software.testato.domain.entity.Hierarchy;
import com.che.software.testato.domain.entity.creation.HierarchyCreation;
import com.che.software.testato.domain.entity.search.HierarchySearch;

/**
 * JDBC implementation of the DAO interface dedicated to the hierarchies
 * management.
 * 
 * @author Clement HELIOU (clement.heliou@che-software.com).
 * @copyright Che Software.
 * @license GNU General Public License.
 * @see AbstractDAO, IHierarchyDAO.
 * @since July, 2011.
 * 
 *        This file is part of Testato.
 * 
 *        Testato is free software: you can redistribute it and/or modify it
 *        under the terms of the GNU General Public License as published by the
 *        Free Software Foundation, either version 3 of the License, or (at your
 *        option) any later version.
 * 
 *        Testato is distributed in the hope that it will be useful, but WITHOUT
 *        ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
 *        FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License
 *        for more details.
 * 
 *        You should have received a copy of the GNU General Public License
 *        along with Testato. If not, see <http://www.gnu.org/licenses/>.
 * 
 *        Testato's logo is a creation of Arrioch
 *        (http://arrioch.deviantart.com/) and it's distributed under the terms
 *        of the Creative Commons License.
 */
@Repository("hierarchyDAO")
public class HierarchyDAO extends AbstractDAO implements IHierarchyDAO {

	/**
	 * Constants.
	 */
	private static final Logger LOGGER = Logger.getLogger(HierarchyDAO.class);

	/**
	 * Creates a hierarchy from a bean of criterions.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param creationBean the bean of criterions.
	 * @since July, 2011.
	 * @throws HierarchyCreationDAOException if an error occurs during the
	 *         creation.
	 */
	@Override
	public void createHierarchyFromBean(HierarchyCreation creationBean) throws HierarchyCreationDAOException {
		LOGGER.debug("createHierarchyFromBean().");
		Connection connection = null;
		try {
			connection = getDataSource().getConnection();
			connection.setAutoCommit(false);
			getQueryRunner().update(connection, "INSERT INTO hirearchy(hierarchy_id, creation_date, last_update_date, user_id, high_level_goal) VALUES(nextval('hierarchy_seq'), NOW(), NOW(), ?, ?) ", new Object[] { creationBean.getUser().getUserId(), creationBean.getHighLevelGoal() });
			Integer createdHierarchyId = (Integer) getQueryRunner().query(connection, "SELECT MAX(hierarchy_id)::int AS hierarchy_id FROM hirearchy ", new ScalarHandler("hierarchy_id"));
			getQueryRunner().update(connection, "INSERT INTO hierarchy_version(version_id, hierarchy_id) VALUES(?, ?) ", new Object[] { creationBean.getVersion().getVersionId(), createdHierarchyId });
			getQueryRunner().update(connection, "INSERT INTO action_plan(action_plan_id) VALUES(nextval('action_plan_seq')) ");
			Integer createdActionPlanId = (Integer) getQueryRunner().query(connection, "SELECT MAX(action_plan_id)::int AS action_plan_id FROM action_plan ", new ScalarHandler("action_plan_id"));
			getQueryRunner().update(connection, "INSERT INTO hierarchy_action_plan(hierarchy_id, action_plan_id) VALUES(?, ?) ", new Object[] { createdHierarchyId, createdActionPlanId });
			connection.commit();
		} catch (SQLException e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				throw new HierarchyCreationDAOException(e1);
			}
			throw new HierarchyCreationDAOException(e);
		} finally {
			if (null != connection) {
				try {
					connection.close();
				} catch (SQLException e) {
					throw new HierarchyCreationDAOException(e);
				}
			}
		}
	}

	/**
	 * Checks if a hierarchy is already existing for a given version id.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param versionId the version id to check
	 * @return true if a hierarchy is already existing, else false.
	 * @since July, 2011.
	 * @throws HierarchySearchDAOException if an error occurs during the search.
	 */
	@Override
	public boolean isHierarchyExistingFromVersionId(int versionId) throws HierarchySearchDAOException {
		LOGGER.debug("isHierarchyExistingFromVersionId(" + versionId + ").");
		Connection connection = null;
		try {
			connection = getDataSource().getConnection();
			return (Boolean) getQueryRunner().query(connection, "SELECT EXISTS ( SELECT hierarchy_id FROM hierarchy_version WHERE version_id = ? ) AS result ", new ScalarHandler("result"), new Object[] { versionId });
		} catch (SQLException e) {
			throw new HierarchySearchDAOException(e);
		} finally {
			if (null != connection) {
				try {
					connection.close();
				} catch (SQLException e) {
					throw new HierarchySearchDAOException(e);
				}
			}
		}
	}

	/**
	 * Hierarchy search from a bean of criterions.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param searchBean the criterions to use for the search.
	 * @return the resulting object list.
	 * @since July, 2011.
	 * @throws HierarchySearchDAOException if an error occurs during the search.
	 */
	@Override
	public List<Hierarchy> searchHierarchy(HierarchySearch searchBean) throws HierarchySearchDAOException {
		LOGGER.debug("searchHierarchy().");
		Connection connection = null;
		try {
			connection = getDataSource().getConnection();
			List<Object> params = new ArrayList<Object>();
			return getQueryRunner().query(connection, getHierarchySearchQueryFromCriterion(searchBean, params), new BeanListHandler<Hierarchy>(Hierarchy.class), params.toArray());
		} catch (SQLException e) {
			throw new HierarchySearchDAOException(e);
		} finally {
			if (null != connection) {
				try {
					connection.close();
				} catch (SQLException e) {
					throw new HierarchySearchDAOException(e);
				}
			}
		}
	}

	/**
	 * Recovery of the hierarchy search query from criterion.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param searchBean the object containing the criterions.
	 * @param params the parameters list corresponding to the built query.
	 * @return the built query.
	 * @since July, 2011.
	 */
	private String getHierarchySearchQueryFromCriterion(HierarchySearch searchBean, List<Object> params) {
		LOGGER.debug("getHierarchySearchQueryFromCriterion().");
		setWhereClauseEnabled(false);
		StringBuilder sBuilder = new StringBuilder("SELECT hierarchy_id AS hierarchyId, creation_date AS creationDate, high_level_goal AS highLevelGoal, action_plan_id AS actionPlanId, last_update_date AS lastUpdateDate, user_id AS responsableUserId, version_id AS versionId FROM hirearchy JOIN hierarchy_version USING(hierarchy_id) JOIN hierarchy_action_plan USING(hierarchy_id) ");
		if (null != searchBean && null != searchBean.getIterationId()) {
			sBuilder.append("JOIN hierarchy_prioritization USING(hierarchy_id) JOIN iteration USING (prioritization_id) ");
			sBuilder.append(getWhereClauseBegin());
			sBuilder.append("iteration_id = ? ");
			params.add(searchBean.getIterationId());
		}
		if (null != searchBean && searchBean.isPrioritizedExcluded()) {
			sBuilder.append(getWhereClauseBegin());
			sBuilder.append("NOT EXISTS( SELECT prioritization_id FROM hierarchy_prioritization hp WHERE hp.hierarchy_id = hierarchy_id ) ");
		}
		return sBuilder.toString();
	}
}